package DBLayer;

import ModelLayer.*;
import java.sql.*;
import java.util.ArrayList;

public class DBPerson {
	private Connection con;

	public DBPerson() {
		con = DbConnection1.getInstance().getDBcon();
	}

	//get all Persons
	public ArrayList<Person> getAllPersons(boolean retrieveAssociation)
	{
		return miscWhere("", retrieveAssociation);
	}

	public Person findPerson(int personID, boolean retriveAssociation)
	{   
		String wClause = "  personID = '" + personID + "'";
		return singleWhere(wClause, retriveAssociation);
	}

	public int insertPerson(Person per) throws Exception
	{  //call to get the next personID number
		int nextPersonID = GetMax.getMaxId("Select max(personID) from Person");
		nextPersonID = nextPersonID + 1;
		System.out.println("next id = " +  nextPersonID);

		int rc = -1;
		String query="INSERT INTO Person(name, addresse, email, customerType, empPosition, zipCode, phone, personID)  VALUES('"+
				
				per.getName()  + "','"  +
				per.getAddresse()  + "','"  +
				per.getEmail()  + "','"  +
				per.getCustomerType()  + "','"  +
				per.getEmpPosition()  + "','"  +
				per.getZipCode()  + "','"  +
				per.getPhone()  + "','"  +
				nextPersonID + "')";


		System.out.println("insert : " + query);
		try{ // insert new person
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//end try
		catch(SQLException ex){
			System.out.println("Person ikke oprettet");
			throw new Exception ("Person is not inserted correct");
		}
		return(rc);
	}

	public int updatePerson(Person per)
	{
		Person perObj  = per;
		int rc=-1;


		String query="UPDATE employee SET "+
				"name ='"+ perObj.getName() + "', "+
				"address ='"+ perObj.getAddresse() + "', " +
				"zipcode ='"+ perObj.getZipCode() + "', " +
				"Phone ='"+ perObj.getPhone() + ", " +
				"email ='"+ perObj.getEmail() + "' " +
				"WHERE personID = '"+ perObj.getPersonID() + "'";
		System.out.println("Update query:" + query);
		try{ // update person
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);

			stmt.close();
		}//end try
		catch(Exception ex){
			System.out.println("Update exception in person db: "+ex);
		}
		return(rc);
	}

	public int delete(int personID)
	{
		int rc=-1;

		String query="DELETE FROM person WHERE personID = '" +
				personID + "'";
		System.out.println(query);
		try{ // delete from person
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//end try	
		catch(Exception ex){
			System.out.println("Delete exception in Person db: "+ex);
		}
		return(rc);
	}

	public Person searchPersonName(String attValue, boolean retriveAssociation)
	{
		String wClause = "name = '" + attValue + "'";
		System.out.println("SearchPerson " + wClause);
		return singleWhere(wClause, retriveAssociation);
	}

	private ArrayList<Person> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<Person> list = new ArrayList<Person>();	

		String query =  buildQuery(wClause);

		try{ // read the person from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);


			while( results.next() ){
				Person perObj = new Person();
				perObj = buildPerson(results);	
				list.add(perObj);	
			}//end while
			stmt.close();     
			if(retrieveAssociation)
			{   //The city is to be build as well
				for(Person perObj : list){
					DBCity dbCity = new DBCity();
					City city = dbCity.findCity(perObj.getCity().getZipCode(), false);
					System.out.println("City is seleceted ");
					perObj.setCity(city);
				}
			}//end if   

		}//end try	
		catch(Exception e){
			System.out.println("Query exception - select: "+e);
			e.printStackTrace();
		}
		return list;
	}

	private Person singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Person perObj = new Person();

		String query =  buildQuery(wClause);
		System.out.println(query);
		try{ // read the person from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if( results.next() ){
				perObj = buildPerson(results);
				//Association is to be build
				stmt.close();
				if(retrieveAssociation)
				{   //The city is to be build as well
					DBCity dbCity = new DBCity();
					City city = dbCity.findCity(perObj.getCity().getZipCode(), false);
					System.out.println("City is seleceted ");
					perObj.setCity(city);
				}			
			}
			else{ //no person was found
				perObj = null;
			}
		}//end try	
		catch(Exception e){
			System.out.println("Query exception: "+e);
		}
		return perObj;
	}

	//method to build the query
	private String buildQuery(String wClause)
	{
		String query="SELECT *  FROM Person";

		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;

		return query;
	}

	//method to build a person object
	private Person buildPerson(ResultSet results)
	{   Person perObj = new Person();
	try{ // the columns from the table person  are used
		perObj.setName(results.getString("name"));
		perObj.setAddresse(results.getString("addresse"));
		perObj.setEmail(results.getString("email"));
		perObj.setCustomerType(results.getString("customerType"));
		perObj.setEmpPosition(results.getString("empPosition"));		
		perObj.setZipCode(results.getInt("zipCode"));
		perObj.setPhone(results.getString("phone"));
		perObj.setPersonID(results.getInt("personID"));
		perObj.setCity(new City(results.getInt("zipCode")));
	}
	catch(Exception e)
	{
		System.out.println("error in building the person object");
	}
	return perObj;
	}


}
